﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using FOModels.OnlineDeposit;
namespace DataAccess.OnlineDeposit
{
    public class OnlineDepositDAL
    {
        private SQLDataAccess db;
        public OnlineDepositDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from OnlineDeposit order by OnlineDepositID desc");
        }
        public DataTable getTermDemandSavingDefaultByProvider(int CompanyID, int langid)
        {
            return db.exeSelect("Select * from OnlineDeposit sa join OnlineDepositLang al on sa.OnlineDepositID = al.OnlineDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableTermDemandInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.TermSavingsCateID = 1 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and  ta.[Month] = 1 and sa.Display = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }

        public DataTable getTermDemandSavingDefault(int langid)
        {
            return db.exeSelect("Select * from OnlineDeposit sa join OnlineDepositLang al on sa.OnlineDepositID = al.OnlineDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableTermDemandInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.TermSavingsCateID = 1 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and  ta.[Month] = 1 and sa.Display = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + "");
        }
        public DataTable getOnlineDeposiList(int langid)
        {
            return db.exeSelect("Select * from OnlineDeposit sa join Company co on sa.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where co.Active = 1 and cla.LangID = " + langid + "");
        }
        public DataTable getTermDemandCate(int langid)
        {
            return db.exeSelect("Select * from TermSavingsCate te join TermSavingsCateLang tel on te.TermSavingsCateID = tel.TermSavingsCateID where LangID = " + langid + "");
        }

        public DataTable getOnlineDeposit(int cate, string balance, int month, int langid)
        {
            return db.exeSelect("Select * from OnlineDeposit sa join OnlineDepositLang al on sa.OnlineDepositID = al.OnlineDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableTermDemandInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.TermSavingsCateID = " + cate + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and  ta.[Month] = " + month + " and sa.Display = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + "");
        }

        public DataTable getOnlineDepositByID(int OnlineDepositID, int langid)
        {
            return db.exeSelect("select * from OnlineDeposit ca join OnlineDepositLang ol on ca.OnlineDepositID = ol.OnlineDepositID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.OnlineDepositID = " + OnlineDepositID + " and cl.LangID = " + langid + " and ol.LangID = " + langid + "");
        }
        public DataTable getOnlineDepositByIDManager(int OnlineDepositID, int langid)
        {
            return db.exeSelect("select * from OnlineDeposit ca join OnlineDepositLang ol on ca.OnlineDepositID = ol.OnlineDepositID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.OnlineDepositID = " + OnlineDepositID + " and cl.LangID = " + langid + " and ol.LangID = " + langid + "");
        }
        public int UpdateOnlineDeposit(OnlineDepositModels models)
        {
            return db.exeUpdate("update OnlineDeposit set OnlineDepositName = N'" + models.OnlineDepositName + "',MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ",CompanyID= " + models.CompanyID + ",Display='" + models.Display + "',TermSavingsCateID = " + models.TermSavingsCateID + " where OnlineDepositID = " + models.OnlineDepositID + "");
        }
        public int InsertOnlineDeposit(OnlineDepositModels models)
        {
            return db.exeUpdate("insert into OnlineDeposit values (N'" + models.OnlineDepositName + "'," + models.CompanyID + "," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.TermSavingsCateID + ",'" + models.Display + "')");
        }
        public int DeleteOnlineDeposit(int OnlineDepositID)
        {
            return db.exeUpdate("Delete from OnlineDeposit where OnlineDepositID = " + OnlineDepositID + "");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("Select * from OnlineDeposit sa join OnlineDepositLang ol on sa.OnlineDepositID = ol.OnlineDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID join Language la on ol.LangID = la.ID where ol.LangID = " + langid + " and cla.LangID = " + langid + "");
        }
        public DataTable gettermdemandsavingrelati(int CompanyID, int OnlineDepositID, int langid)
        {
            return db.exeSelect("select * from OnlineDeposit ca join OnlineDepositLang onl on ca.OnlineDepositID = onl.OnlineDepositID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where co.Active = 1 and ca.CompanyID = " + CompanyID + " and ca.OnlineDepositID !=" + OnlineDepositID + " and onl.LangID = " + langid + " and cla.LangID = " + langid + "");
        }
        public DataTable getTermDemandSavingListByProvider(int cateid, string balance, int month, int CompanyID, int langid)
        {
            return db.exeSelect("select * from OnlineDeposit sa join OnlineDepositLang al on sa.OnlineDepositID = al.OnlineDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableTermDemandInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.TermSavingsCateID = " + cateid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and  ta.[Month] = " + month + " and sa.Display = 1 and cl.LangID = " + langid + " and al.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public int UpdateOnlineDepositLang(OnlineDepositModels models, int langid)
        {
            return db.exeUpdate("update OnlineDepositLang set OnlineDepositName = N'" + models.OnlineDepositName + "',Description=N'" + models.Description + "',Benefit=N'" + models.Benefit + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "' where OnlineDepositID = " + models.OnlineDepositID + " and LangID =" + langid + "");
        }
        public int InsertOnlineDepositLang(OnlineDepositModels models, int langid)
        {
            return db.exeUpdate("insert into OnlineDepositLang values (" + models.OnlineDepositID + "," + langid + ",N'" + models.OnlineDepositName + "',N'" + models.Description + "',N'" + models.Benefit + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }
        public int DeleteOnlineDepositLang(int OnlineDepositID)
        {
            return db.exeUpdate("Delete from OnlineDepositLang where OnlineDepositID = " + OnlineDepositID + "");
        }

        public int DeleteOnlineDepositApply(int OnlineDepositApplyID)
        {
            return db.exeUpdate("Delete from OnlineDepositApply where OnlineDepositApplyID = " + OnlineDepositApplyID + "");
        }
        public int UpdateStatus(OnlineDepositApplyModels card)
        {
            return db.exeUpdate("update OnlineDepositApply set StatusID= " + card.StatusID + " where OnlineDepositApplyID = " + card.OnlineDepositApplyID + "");
        }
        public int OnlineDepositApply(int OnlineDepositID, OnlineDepositApplyModels app)
        {
            return db.exeUpdate("insert into OnlineDepositApply(OnlineDepositID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + OnlineDepositID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select * from OnlineDepositApply ca join OnlineDeposit c on ca.OnlineDepositID = c.OnlineDepositID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.OnlineDepositApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from OnlineDepositApply ca join [OnlineDeposit] c on ca.OnlineDepositID = c.OnlineDepositID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatus(int statusid)
        {
            return db.exeSelect("select * from OnlineDepositApply ca join [OnlineDeposit] c on ca.OnlineDepositID = c.OnlineDepositID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1 and ca.StatusID = " + statusid + "");
        }
    }
}
